    package com.springapp.dao.dao.impl;
    import com.springapp.dao.dao.ICommandeDAO;
import com.springapp.model.Commande;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

    /**
     * Created by Thomas on 22/06/14.
     */
    @Repository
    public class CommandeDAO implements ICommandeDAO {

        @Qualifier("dataSource") //Fait référence au datasource dans le mvc-dispatcher.xml
        @Autowired
        private DataSource dataSource;

        public void setDataSource(DataSource dataSource) {
            this.dataSource = dataSource;
        }


        @Override
        public void insert(Commande obj) {

        }

        @Override
        public void delete(Commande obj) {

        }

        @Override
        public boolean update(String sRef) {
            Boolean bUpdateOk = false;
            String sql="" +
                    "UPDATE Commande " +
                    "SET IDCmdStatut=2 "+
                    "WHERE IDCommande=(SELECT IDCommande FROM Commande WHERE RefCommande ='"+sRef+"')";
            Connection conn = null;
            try {
                conn=dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.executeUpdate();
                bUpdateOk = true;
                ps.close();
            }
            catch (SQLException e) {
                e.printStackTrace();

            }
            return bUpdateOk;
        }

        @Override
        public boolean updateStatusCmdAfected(String sLogin) {
            Boolean bUpdateOk = false;
            String sql="UPDATE Commande\n" +
                    "SET IDUtilisateur=(\n" +
                    "\tSELECT IDUtil\n" +
                    "\tFROM Utilisateur\n" +
                    "\tWHERE login='"+sLogin+"')\n" +
                    "\t\tWHERE IDCommande=(\n" +
                    "\t\t\tSELECT TOP(1) IDCommande\n" +
                    "\t\t\tFROM Commande\n" +
                    "\t\t\tWHERE IDUtilisateur IS NULL\n" +
                    "\t\t\tORDER BY dateLivraisonDemandee ASC)";
            Connection conn = null;
            try {
                conn=dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.executeUpdate();
                bUpdateOk = true;
                ps.close();
            }
            catch (SQLException e) {
                e.printStackTrace();

            }
            return bUpdateOk;
        }






        //PARTIE POUE LE CHEF PREPA
        public List<Commande> getAllByRef(String sRef) {


            String sql="SELECT Cmd.IDCommande,Cmd.RefCommande, Cmd.dateCrea, Cmd.dateMaj, Cmd.dateLivraison, Cmd.montantHT, Cmd.TVA,Cmd.montantTTC, cmd.datePlanif, cmd.dateLivraisonDemandee,CStat.libelle AS 'Statut', concat(Util.prenomUtil, ' ', Util.nomUtil) AS nomPrenom\n" +
                        "FROM Commande Cmd\n" +
                        "INNER JOIN Cmd_Statut CStat ON cmd.IDCmdStatut=CStat.IDCmdStatut\n" +
                        "INNER JOIN utilisateur Util ON cmd.IDUtilisateur=Util.IDUtil\n" +
                        "WHERE Util.IDUtil!=1 AND Cmd.RefCommande='"+sRef+"'\n" +
                        "ORDER BY CStat.IDCmdStatut ASC, Cmd.dateCrea ASC";
            Connection conn=null;
            List<Commande> lstCmd=new ArrayList<Commande>();

            try {
                conn=dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet result=ps.executeQuery();
                while(result.next()){
                    Commande commande=new Commande(
                        result.getInt("IDCommande"),
                        result.getString("RefCommande"),
                        result.getDate("dateCrea"),
                        result.getDate("dateMaj"),
                        result.getDate("dateLivraison"),
                        result.getFloat("montantHT"),
                        result.getFloat("TVA"),
                        result.getFloat("montantTTC"),
                        result.getDate("datePlanif"),
                        result.getDate("dateLivraisonDemandee"),
                        result.getString("Statut"),
                        result.getString("nomPrenom")
                        );
                    lstCmd.add(commande);
                }
                result.close();
                ps.close();


            } catch (SQLException e) {
                e.printStackTrace();
            }

            return lstCmd;
        }


        public List<Commande> getLstCmdInPrepa(String _sLogin) {
            String sql="SELECT Cmd.RefCommande, Cmd.dateCrea, Cmd.dateMaj, Cmd.dateLivraison, Cmd.montantHT, Cmd.montantTTC, Cmd.TVA, CStat.libelle\n" +
            "FROM Commande Cmd\n" +
                    "INNER JOIN Cmd_Statut CStat ON cmd.IDCmdStatut=CStat.IDCmdStatut\n" +
                    "INNER JOIN utilisateur Util ON cmd.IDUtilisateur=Util.IDUtil\n" +
                    "WHERE CStat.IDCmdStatut=1 AND Cmd.IDUtilisateur=(\n" +
                    "  SELECT IDUtil\n" +
                    "  FROM Utilisateur\n" +
                    "  WHERE login='"+_sLogin+"')\n" +
                    "ORDER BY CStat.IDCmdStatut ASC, Cmd.dateCrea ASC";
            Connection conn = null;
            List<Commande> lstCmdInPrepa=new ArrayList<Commande>();

            try {
                conn=dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet result=ps.executeQuery();
                while(result.next()){
                    Commande Commande =new Commande(
                            result.getInt("IDCommande"),
                            result.getString("RefCommande"),
                            result.getDate("dateCrea"),
                            result.getDate("dateMaj"),
                            result.getDate("dateLivraison"),
                            result.getFloat("montantHT"),
                            result.getFloat("montantTTC"),
                            result.getFloat("TVA"),
                            result.getDate("datePlanif"),
                            result.getDate("dateLivraisonDemandee"),
                            result.getString("libelle"),
                            ""

                    );
                    lstCmdInPrepa.add(Commande);
                }
                result.close();
                ps.close();


            } catch (SQLException e) {
                e.printStackTrace();
            }

            return lstCmdInPrepa;
        }

        public List<Commande> getLstCmdNotPrepared() {

            String sql="" +
                    "SELECT Cmd.RefCommande, Cmd.dateCrea, CStat.libelle\n" +
                    "FROM Commande Cmd\n" +
                    "INNER JOIN Cmd_Statut CStat ON cmd.IDCmdStatut=CStat.IDCmdStatut\n" +
                    "INNER JOIN utilisateur Util ON cmd.IDUtilisateur=Util.IDUtil\n" +
                    "WHERE CStat.IDCmdStatut=1\n" +
                    "ORDER BY CStat.IDCmdStatut ASC, Cmd.dateCrea ASC";
            Connection conn = null;
            List<Commande> lstCmd=new ArrayList<Commande>();

            try {
                conn=dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet result=ps.executeQuery();
                while(result.next()){
                    Commande commande =new Commande(
                            result.getString("RefCommande"),
                            result.getDate("dateCrea"),
                            result.getString("libelle")
                    );
                    lstCmd.add(commande);
                }
                result.close();
                ps.close();


            } catch (SQLException e) {
                e.printStackTrace();
            }

            return lstCmd;
        }

    /*
    Cette fonction permet d'afficher un liste de commande selon sa reference, sa date de création et  libellé
    l'ID 1 du statut de la commande correspond au statut "En-cours de préparation" (abreviation EAP)
    */

        public List<Commande> getLstCmd(String sLogin) {
            String sql="" +
                    "SELECT Cmd.IDCommande, Cmd.RefCommande, Cmd.dateCrea,  CStat.libelle\n" +
                    "FROM Commande Cmd\n" +
                    "INNER JOIN Cmd_Statut CStat ON cmd.IDCmdStatut=CStat.IDCmdStatut\n" +
                    "INNER JOIN utilisateur Util ON cmd.IDUtilisateur=Util.IDUtil\n" +
                    "WHERE Util.login="+"'" +sLogin+"'"+ "AND CStat.IDCmdStatut!=1 \n" +
                    "ORDER BY CStat.IDCmdStatut ASC, Cmd.dateCrea ASC";
            Connection conn = null;
            List<Commande> lstCmd=new ArrayList<Commande>();

            try {
                conn=dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet result=ps.executeQuery();
                while(result.next()){
                    Commande commande =new Commande(
                            result.getString("RefCommande"),
                            result.getDate("dateCrea"),
                            result.getString("libelle")
                    );
                    lstCmd.add(commande);
                }
                result.close();
                ps.close();


            } catch (SQLException e) {
                e.printStackTrace();
            }

            return lstCmd;
        }
    }
